/*
 * To change this template, choose Tools | Templates
 * and open the template in the editor.
 */
package ngramassigment.database;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.HashMap;
import java.util.Map;
import java.util.logging.Level;
import java.util.logging.Logger;
import ngramassigment.Config;

/**
 *
 * @author Tran
 */
public class DatabaseManager {

    private static DatabaseManager instance = null;
    private Connection connection = null;

    public static DatabaseManager getInstance() {
        if (instance == null) {
            instance = new DatabaseManager();
        }
        return instance;
    }

    public DatabaseManager() {
    }

    public boolean createTableVocabulary() {
        connection = DatabaseFunction.getInstance().connectDatabase();
        return DatabaseFunction.getInstance().executeCreate(DatabaseConfig.SQL_CREATE_TABLE_VOCA, connection);
    }

    public boolean createTableBiGram() {
        connection = DatabaseFunction.getInstance().connectDatabase();
        return DatabaseFunction.getInstance().executeCreate(DatabaseConfig.SQL_CREATE_TABLE_BI_GRAM, connection);
    }

    public boolean createTableTriGram() {
        connection = DatabaseFunction.getInstance().connectDatabase();
        return DatabaseFunction.getInstance().executeCreate(DatabaseConfig.SQL_CREATE_TABLE_TRI_GRAM, connection);
    }

    public boolean createTableTetGram() {
        connection = DatabaseFunction.getInstance().connectDatabase();
        return DatabaseFunction.getInstance().executeCreate(DatabaseConfig.SQL_CREATE_TABLE_TET_GRAM, connection);
    }

    public boolean createTablePenGram() {
        connection = DatabaseFunction.getInstance().connectDatabase();
        return DatabaseFunction.getInstance().executeCreate(DatabaseConfig.SQL_CREATE_TABLE_PEN_GRAM, connection);
    }

    public void updateDataOfVocabulary(HashMap<String, Integer> hmID, HashMap<String, Integer> hmCounter) {
        try {
            connection = DatabaseFunction.getInstance().connectDatabase();
            connection.setAutoCommit(false);
            for (Map.Entry<String, Integer> entry : hmID.entrySet()) {
                String word = entry.getKey();
                //System.out.println("Store word: "+word);
                Integer id = entry.getValue();
                String sql = "INSERT INTO " + DatabaseConfig.TB_VOCA_NAME + " ("
                        + DatabaseConfig.TB_VOCA_FIELD_ID + ", " + DatabaseConfig.TB_VOCA_FIELD_WORD + ", " + DatabaseConfig.TB_VOCA_FIELD_COUNT + ") "
                        + "VALUES (" + id + ", '" + word + "', " + (int) hmCounter.get(word) + ");";
                DatabaseFunction.getInstance().executeInsertUpdateDelete(sql, connection);
            }
            connection.commit();
            DatabaseFunction.getInstance().closeDatabase(connection);
        } catch (SQLException ex) {
            //Logger.getLogger(DatabaseManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public HashMap<String, Integer> selectAllDataOfVocabulary() {
        HashMap<String, Integer> hmId = new HashMap<String, Integer>();        
        connection = DatabaseFunction.getInstance().connectDatabase();

        String sql = "SELECT * FROM " + DatabaseConfig.TB_VOCA_NAME + " ;";
        try {
            PreparedStatement pst = connection.prepareStatement(sql);
            ResultSet rs=pst.executeQuery();
            while (rs.next()) {
                int id = rs.getInt(DatabaseConfig.TB_VOCA_FIELD_ID);
                String word = rs.getString(DatabaseConfig.TB_VOCA_FIELD_WORD); 
                hmId.put(word, id);
            }
            rs.close();
        } catch (SQLException ex) {
            //Logger.getLogger(DatabaseManager.class.getName()).log(Level.SEVERE, null, ex);
        }

        DatabaseFunction.getInstance().closeDatabase(connection);
        return hmId;
    }

    public void updateDataNGram(HashMap<Double, Integer> hmCounterGram, int typeGram) {
        try {
            connection = DatabaseFunction.getInstance().connectDatabase();
            connection.setAutoCommit(false);
            String tbName = "";
            String fieldId = "";
            String fieldCount = "";
            switch (typeGram) {
                case 2:
                    tbName = DatabaseConfig.TB_BIGR_NAME;
                    fieldId = DatabaseConfig.TB_BIGR_FIELD_ID;
                    fieldCount = DatabaseConfig.TB_BIGR_FIELD_COUNT;
                    break;
                case 3:
                    tbName = DatabaseConfig.TB_TRIGR_NAME;
                    fieldId = DatabaseConfig.TB_TRIGR_FIELD_ID;
                    fieldCount = DatabaseConfig.TB_TRIGR_FIELD_COUNT;
                    break;
                case 4:
                    tbName = DatabaseConfig.TB_TETGR_NAME;
                    fieldId = DatabaseConfig.TB_TETGR_FIELD_ID;
                    fieldCount = DatabaseConfig.TB_TETGR_FIELD_COUNT;
                    break;
                case 5:
                    tbName = DatabaseConfig.TB_PENGR_NAME;
                    fieldId = DatabaseConfig.TB_PENGR_FIELD_ID;
                    fieldCount = DatabaseConfig.TB_PENGR_FIELD_COUNT;
                    break;
            }

            for (Map.Entry<Double, Integer> entry : hmCounterGram.entrySet()) {

                //System.out.println("Store word: "+word);
                Double id = entry.getKey();
                Integer value = entry.getValue();
                if (tbName.length() > 1) {
                    String sql = "INSERT INTO " + tbName + " ("
                            + fieldId + ", " + fieldCount + ") "
                            + "VALUES (" + id + " , " + value + ");";
                    DatabaseFunction.getInstance().executeInsertUpdateDelete(sql, connection);
                }
            }
            connection.commit();
            DatabaseFunction.getInstance().closeDatabase(connection);
        } catch (SQLException ex) {
            //Logger.getLogger(DatabaseManager.class.getName()).log(Level.SEVERE, null, ex);
        }
    }

    public int calculateSumCountInTable(int typeGram, Connection con) {
        int result = 0;

        String tbName = "";
        String fieldCount = "";
        switch (typeGram) {
            case 2:
                tbName = DatabaseConfig.TB_BIGR_NAME;
                fieldCount = DatabaseConfig.TB_BIGR_FIELD_COUNT;
                break;
            case 3:
                tbName = DatabaseConfig.TB_TRIGR_NAME;
                fieldCount = DatabaseConfig.TB_TRIGR_FIELD_COUNT;
                break;
            case 4:
                tbName = DatabaseConfig.TB_TETGR_NAME;
                fieldCount = DatabaseConfig.TB_TETGR_FIELD_COUNT;
                break;
            case 5:
                tbName = DatabaseConfig.TB_PENGR_NAME;
                fieldCount = DatabaseConfig.TB_PENGR_FIELD_COUNT;
                break;
        }

        String sql = "SELECT SUM( " + fieldCount + " ) as Total FROM " + tbName + " ;";
        try {
            PreparedStatement pst = con.prepareStatement(sql);
                ResultSet rs=pst.executeQuery();
             
            if (rs.next()) {
                result = rs.getInt("Total");
            }
            pst.close();
            rs.close();
        } catch (SQLException ex) {
            //Logger.getLogger(DatabaseManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        return result;
    }

    public void calculateProbInDatabase() {
        connection = DatabaseFunction.getInstance().connectDatabase();
        for (int i = 0; i < Config.MAX_NUM_TYPE_GRAM; i++) {
            int typeGram = i + 2;
            int total = calculateSumCountInTable(i + 2, connection);
            String tbName = "";
            String fieldId = "";
            String fieldCount = "";
            String fieldProb = "";
            switch (typeGram) {
                case 2:
                    tbName = DatabaseConfig.TB_BIGR_NAME;
                    fieldId = DatabaseConfig.TB_BIGR_FIELD_ID;
                    fieldCount = DatabaseConfig.TB_BIGR_FIELD_COUNT;
                    fieldProb = DatabaseConfig.TB_BIGR_FIELD_PROB;
                    break;
                case 3:
                    tbName = DatabaseConfig.TB_TRIGR_NAME;
                    fieldId = DatabaseConfig.TB_TRIGR_FIELD_ID;
                    fieldCount = DatabaseConfig.TB_TRIGR_FIELD_COUNT;
                    fieldProb = DatabaseConfig.TB_TRIGR_FIELD_PROB;
                    break;
                case 4:
                    tbName = DatabaseConfig.TB_TETGR_NAME;
                    fieldId = DatabaseConfig.TB_TETGR_FIELD_ID;
                    fieldCount = DatabaseConfig.TB_TETGR_FIELD_COUNT;
                    fieldProb = DatabaseConfig.TB_TETGR_FIELD_PROB;
                    break;
                case 5:
                    tbName = DatabaseConfig.TB_PENGR_NAME;
                    fieldId = DatabaseConfig.TB_PENGR_FIELD_ID;
                    fieldCount = DatabaseConfig.TB_PENGR_FIELD_COUNT;
                    fieldProb = DatabaseConfig.TB_PENGR_FIELD_PROB;
                    break;
            }
            updateProbInDatabase(total, tbName, fieldId, fieldCount, fieldProb, connection);
        }
        DatabaseFunction.getInstance().closeDatabase(connection);
    }

    public void updateProbInDatabase(int total, String tbName, String fieldID, String fieldCount, String fieldProb, Connection con) {

        String sql = "SELECT * FROM " + tbName + " ;";
        String sqlUpdate = "UPDATE " + tbName + " SET " + fieldProb + " = ? WHERE " + fieldID + " = ?;";
        try {
            con.setAutoCommit(false);
            PreparedStatement pst = con.prepareStatement(sql);
            ResultSet rs=pst.executeQuery();
            while (rs.next()) {
                double id = rs.getLong(fieldID);
                int count = rs.getInt(fieldCount);
                double prob = (count * 1.0) / (total * 1.0);

                PreparedStatement pstmt = null;
                pstmt = con.prepareStatement(sqlUpdate);
                pstmt.setDouble(1, prob);
                pstmt.setDouble(2, id);
                pstmt.executeUpdate();
                pstmt.close();
            }
            rs.close();
            con.commit();
        } catch (SQLException ex) {
            //Logger.getLogger(DatabaseManager.class.getName()).log(Level.SEVERE, null, ex);
        } finally {

        }
    }
    
    public int countOfNGramFull(double index, int typeGram) {
        //String sql = "SELECT SUM( " + DatabaseConfig.LIST_TB_FIELD_COUNT.get(typeGram-2) + " ) as Total FROM " + DatabaseConfig.LIST_TB_NAME.get(typeGram-2) + " ;";
        connection = DatabaseFunction.getInstance().connectDatabase();
        int result = 0;
        String sql = "SELECT * FROM "+ getTableNameFromTypeGram(typeGram)+" WHERE "+getTableFieldId(typeGram)+" = ? ;";
        try {
            PreparedStatement pst = connection.prepareStatement(sql);
            pst.setDouble(1, index);
            ResultSet rs=pst.executeQuery();
             
            if (rs.next()) {
                result = rs.getInt(getTableFieldCount(typeGram));
            }
            pst.close();
            rs.close();
        } catch (SQLException ex) {
            //Logger.getLogger(DatabaseManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        DatabaseFunction.getInstance().closeDatabase(connection);
        return result;
    }
    
    
    public int countOfNGramAPart(double index, double lastIndex, int typeGram) {
        
        connection = DatabaseFunction.getInstance().connectDatabase();
        int result = 0;
        double minPivot = index - lastIndex + 1;
        double maxPivot = index - lastIndex + Config.MAX_WORD ;
        
        String sql = "SELECT SUM( " + getTableFieldCount(typeGram) + " ) as Total "
                + "FROM " + getTableNameFromTypeGram(typeGram) + " "
                + "WHERE "+ getTableFieldId(typeGram)+" BETWEEN ? AND ?;";
        try {
            PreparedStatement pst = connection.prepareStatement(sql);
            pst.setDouble(1, minPivot);
            pst.setDouble(2, maxPivot);
            ResultSet rs=pst.executeQuery();
             
            if (rs.next()) {
                result = rs.getInt("Total");
            }
            pst.close();
            rs.close();
        } catch (SQLException ex) {
            //Logger.getLogger(DatabaseManager.class.getName()).log(Level.SEVERE, null, ex);
        }
        DatabaseFunction.getInstance().closeDatabase(connection);
        return result;
    }
    
    private String getTableNameFromTypeGram(int typeGram) {
        return DatabaseConfig.LIST_TB_NAME.get(typeGram-2);
    }
    private String getTableFieldId(int typeGram) {
        return DatabaseConfig.LIST_TB_FIELD_ID.get(typeGram-2);
    }
    
    private String getTableFieldCount(int typeGram) {
        return DatabaseConfig.LIST_TB_FIELD_COUNT.get(typeGram-2);
    }
}
